package com.wuhongyu.service.impl;

import com.wuhongyu.entity.*;
import com.wuhongyu.entity.ExcelDataExample.Criteria;
import com.wuhongyu.mapper.ExcelColumnNameMapper;
import com.wuhongyu.mapper.ExcelDataMapper;
import com.wuhongyu.mapper.ExcelMapper;
import com.wuhongyu.service.ExcelService;
import com.wuhongyu.util.MyUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.util.Arrays;
import java.util.List;

@Service("excelService")
public class ExcelServiceimpl implements ExcelService{
	@Autowired
	private ExcelColumnNameMapper excelColumnNameMapper;
	@Autowired
	private ExcelDataMapper excelDataMapper;
	@Autowired
	private ExcelMapper excelMapper;

	/**
	 * 导入excel文件
	 * @param excel
	 * @param excelNum
	 */
	@Override
	public void leadingExcel(MultipartFile excel, Integer excelNum,String name) {
		try {
			//添加excle表到对应的数据库表中
			if(StringUtils.isEmpty(name))
				name = excel.getOriginalFilename();
			Excel excel1 = new Excel();
			excel1.setName(name);
			excel1.setExcelnum(excelNum);
			excelMapper.insertSelective(excel1);
			//增加对应的表头和数据
			List<Object> list = MyUtil.excel2Mysql(excel, excelNum);
			List<ExcelColumnName> columnList = (List<ExcelColumnName>) list.get(0);
			for (ExcelColumnName excelColumnName : columnList) {
				excelColumnNameMapper.insert(excelColumnName);
			}
			List<ExcelData> dataList= (List<ExcelData>) list.get(1);
			for (ExcelData excelData : dataList) {
				excelDataMapper.insert(excelData);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}


	/**
	 * 查询对应excelnum的excel表中的数据
	 * @param excelData
	 * @return
	 */
	@Override
	public List<ExcelData> selectDataList(ExcelData excelData) {
		ExcelDataExample example = new ExcelDataExample();
		Criteria c = example.createCriteria();
		//模糊查询
		if(excelData.getColumn1()!=null && !"".equals(excelData.getColumn1())) {
			c.andColumn1Like("%"+excelData.getColumn1()+"%");
		}else if(excelData.getColumn2()!=null && !"".equals(excelData.getColumn2())) {
			c.andColumn2Like("%"+excelData.getColumn2()+"%");
		}else if(excelData.getColumn3()!=null && !"".equals(excelData.getColumn3())) {
			c.andColumn3Like("%"+excelData.getColumn3()+"%");
		}
		c.andExcelnumEqualTo(excelData.getExcelnum());
		List<ExcelData> dataList = excelDataMapper.selectByExample(example);
		return dataList;
	}

	/**
	 * 清空所有表
	 */
	@Override
	public void cleanAll() {
		excelColumnNameMapper.cleanAll();
		excelDataMapper.cleanAll();
	}

	/**
	 * 通过id删除一行
	 * @param id
	 */
	@Override
	public void deleteDataById(Integer id) {
		excelDataMapper.deleteByPrimaryKey(id);
	}


	/**
	 * 添加或者修改
	 * @param excelData
	 */
	@Override
	public void updateOrInser(ExcelData excelData) {
		if(excelData.getId()!=null && !"".equals(excelData.getId())) {
			excelDataMapper.updateByPrimaryKeySelective(excelData);
		}else {
			excelDataMapper.insertSelective(excelData);
		}
	}

	/**
	 * 删除多行
	 * @param ids
	 */
	@Override
	public void deleteByIds(Integer[] ids) {
		if(ids!=null) {
			List<Integer> idList = Arrays.asList(ids);
			excelDataMapper.deleteByKeys(idList);
		}
	}


	/**
	 * 修改之前的数据回显
	 * @param id
	 * @return
	 */
	@Override
	public ExcelData queryById(Integer id) {
		ExcelData data = excelDataMapper.selectByPrimaryKey(id);
		return data;
	}


	/**
	 * 删除一列
	 * @param id
	 * @param num
	 */
	@Override
	public void deleteColumn(Integer id,Integer num) {
		ExcelColumnNameExample example = new ExcelColumnNameExample();
		example.createCriteria();
		//查询excelNum对应的表头
		List<ExcelColumnName> nameList1 = this.selecColumnNameListByEn(num);
		excelColumnNameMapper.deleteByPrimaryKey(id);
		//查询删除一列之后的所有表头
		List<ExcelColumnName> nameList = excelColumnNameMapper.selectByExample(example);
		//清空数据库
		excelColumnNameMapper.cleanAll();
		//重新将表头放入数据库(重置表头的id)
		for (ExcelColumnName name : nameList) {
			name.setId(null);
			excelColumnNameMapper.insertSelective(name);
		}

		//计算表头的相对与数据表的column的id
		id = id - nameList1.get(0).getId()+1;
		//如果要删除的列刚好是最后一个有数据的列,那么直接让次列为空
		if(id == nameList1.size()) {
			String column = "column"+id;
			excelDataMapper.updateColumn(column,num);
			//否则的话 先将要删除的列为空,再将后面的列向前移动
		}else if(id >=1 && id <nameList1.size()){
			String sql = "";
			for(int i=id; i<10; i++) {
				if(i<9) {
					sql = sql+"column"+i+"="+"column"+(i+1)+",";
				}else {
					sql = sql+"column"+i+"="+"column"+(i+1);
				}
			}
			
			excelDataMapper.updateColumnNull(sql,num);
		}
		
	
	}

	/**
	 * 增加一列
	 * @param excelColumnName
	 */
	@Override
	public void insertColumn(ExcelColumnName excelColumnName) {
		//获取到要增加的列的id
		Integer id = excelColumnName.getId();

		ExcelColumnNameExample example = new ExcelColumnNameExample();
		example.createCriteria();
		//查询出所有表头的名称
		List<ExcelColumnName> nameList = excelColumnNameMapper.selectByExample(example);
		//将这个新表头加入list集合
		nameList.add(id,excelColumnName);
		//清空表头
		excelColumnNameMapper.cleanAll();
		//重新放入数据库(为了重置id)
		for (ExcelColumnName name : nameList) {
			name.setId(null);
			excelColumnNameMapper.insertSelective(name);
		}
		//查出对应的excelNum的表头
		List<ExcelColumnName> nameList1 = this.selecColumnNameListByEn(excelColumnName.getExcelnum());
		//计算相对id
		id = id - nameList1.get(0).getId()+1;
		//讲所有excel_data的数据向后移动一位
		String sql = "";
		for(int i=10; i>id; i--) {
			if(i-1>id) {
				sql = sql+"column"+i+"="+"column"+(i-1)+",";
			}else {
				sql = sql+"column"+i+"="+"column"+(i-1);
			}
		}

		//这里被插入的行数据就留用之前改行的数据0.0  想改的话可以改成任意字符串
		excelDataMapper.updateColumnNull(sql,excelColumnName.getExcelnum());
	}

	/**
	 * 通过excelNum查询出对应的表头
	 * @param excelNum
	 * @return
	 */
	@Override
	public List<ExcelColumnName> selecColumnNameListByEn(Integer excelNum) {
		ExcelColumnNameExample example = new ExcelColumnNameExample();
		example.createCriteria().andExcelnumEqualTo(excelNum);
		return excelColumnNameMapper.selectByExample(example);
	}


	/**
	 * 得到当前数据库中一共储存了的表的excelNum,,,,,这个方法一会应该不会用了
	 * @return
	 */
	@Override
	public Integer getExcelNum() {
		ExcelColumnNameExample example = new ExcelColumnNameExample();
		example.createCriteria();
		List<ExcelColumnName> nameList = excelColumnNameMapper.selectByExample(example);
		if(nameList.size()!=0) {
			return nameList.get(nameList.size()-1).getExcelnum();
		}
		return 0;
	}

	/**
	 *查询当前数据库一共存放了多少张excel表
	 * @return
	 */
	@Override
	public List<Excel> findAll(){
		return excelMapper.selectAll();
	}


	/**
	 * 删除数据库中的某个excel表
	 * @param id
	 */
	@Override
	public void deleteExcel(Integer id){
		//查询出该表所对应的excelNum
		Excel excel = excelMapper.selectByPrimaryKey(id);
		Integer excelnum = excel.getExcelnum();
		//删除该表
		excelMapper.deleteByPrimaryKey(id);
		//删除该excelNum所对应的excelColumnName所对应的数据
		ExcelColumnNameExample excelColumnNameExample = new ExcelColumnNameExample();
		excelColumnNameExample.createCriteria().andExcelnumEqualTo(excelnum);
		excelColumnNameMapper.deleteByExample(excelColumnNameExample);
		//删除excel_data中对应的数据
		ExcelDataExample excelDataExample = new ExcelDataExample();
		excelDataExample.createCriteria().andExcelnumEqualTo(excelnum);
		excelDataMapper.deleteByExample(excelDataExample);
	}

	/**
	 * 修改excel表的名字
	 * @param excel
	 */
	@Override
	public void updateExcel(Excel excel){
		excelMapper.updateByPrimaryKeySelective(excel);
	}

}
